Skip Headers

Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# SET CONSTRAINT[S]

Purpose

Use the `SET` `CONSTRAINTS` statement to specify, for a particular transaction, whether a deferrable constraint is checked following each DML statement or when the transaction is committed.

Prerequisites

To specify when a deferrable constraint is checked, you must have `SELECT` privilege on the table to which the constraint is applied unless the table is in your schema.

Syntax

set_constraints::=

Description of the illustration set_constraints.gif

Semantics

constraint

Specify the name of one or more integrity constraints.

ALL

Specify `ALL` to set all deferrable constraints for this transaction.

IMMEDIATE

Specify `IMMEDIATE` to indicate that the conditions specified by the deferrable constraint are checked immediately after each DML statement.

DEFERRED

Specify `DEFERRED` to indicate that the conditions specified by the deferrable constraint are checked when the transaction is committed.

Note: You can verify the success of deferrable constraints prior to committing them by issuing a `SET` `CONSTRAINTS` `ALL` `IMMEDIATE` statement.

Examples

Setting Constraints: Examples The following statement sets all deferrable constraints in this transaction to be checked immediately following each DML statement:

    SET CONSTRAINTS ALL IMMEDIATE;

The following statement checks three deferred constraints when the transaction is committed. This example fails if the constraints were specified to be `NOT` `DEFERRABLE`.

    SET CONSTRAINTS emp_job_nn, emp_salary_min ,
       hr.jhist_dept_fk@remote DEFERRED;